package com.bupt.kgplatform.mapper;

import com.alibaba.druid.pool.DruidDataSource;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @author ljr
 * @date 2020/10/29 10:53 上午
 */

@Repository
public class DatabaseDao {

    JdbcTemplate jdbcTemplate;

    public void setJdbcTemplate(DruidDataSource dataSource) {
        jdbcTemplate = new JdbcTemplate(dataSource);
    }

    public List getdbs() {
        List<Map<String, Object>> tempList;
        tempList = jdbcTemplate.queryForList("show databases");
        return tempList;
    }

    public List selectdb(String dbname) {
        List<Map<String, Object>> tempList = new ArrayList<>();
        jdbcTemplate.execute(String.format("use %s", dbname));
        return tempList;
    }

    public List getTables(String dbname) {
        List<Map<String, Object>> tempList;
        tempList = jdbcTemplate.queryForList(String.format("show tables in %s", dbname));
        return tempList;
    }

    public List getDescribe(String dbName, String tableName ) {
        List<Map<String, Object>> tempList;
        tempList = jdbcTemplate.queryForList(String.format("describe %s.`%s`", dbName, tableName));
        return tempList;
    }

    public List getColumns(String dbName, String tableName) {
        List<Map<String, Object>> tempList;
        tempList = jdbcTemplate.queryForList(String.format("Select COLUMN_NAME 列名, DATA_TYPE 类型, COLUMN_COMMENT 注释\n" +
                "from INFORMATION_SCHEMA.COLUMNS \n" +
                "where table_name = '%s' \n" +
                "and table_schema = '%s' ", tableName, dbName));
        return tempList;
    }

    public List getReference(String dbName, String tableName) {
        List<Map<String, Object>> tempList;
        tempList = jdbcTemplate.queryForList(String.format("Select COLUMN_NAME 列名, CONSTRAINT_NAME 约束, REFERENCED_TABLE_NAME 外键表名, REFERENCED_COLUMN_NAME 外键列名\n" +
                "from INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n" +
                "where table_name = '%s' \n" +
                "and table_schema = '%s' ", tableName, dbName));
        return tempList;
    }

    public List getPrimary(String dbName, String tableName) {
        List<Map<String, Object>> tempList;
        tempList = jdbcTemplate.queryForList(String.format("Select COLUMN_NAME 列名\n " +
                "from INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n" +
                "where table_name = '%s' \n" +
                "and table_schema = '%s' ", tableName, dbName));
        return tempList;
    }

    public List getData(String dbName, String tableName) {
//        selectdb(dbName);
        List<Map<String, Object>> tempList;
        tempList = jdbcTemplate.queryForList(String.format("select * from %s.`%s`",dbName, tableName));
        return tempList;
    }

    public List getLimitData(String dbName, String tableName, int start, int end) {
//        selectdb(dbName);
        List<Map<String, Object>> tempList;
        tempList = jdbcTemplate.queryForList(String.format("select * from %s.`%s` LIMIT %d,%d", dbName, tableName, start, end));
        return tempList;
    }

    public List getRefData(String dbName, String tableName, String primary, String ref_column) {
//        selectdb(dbName);
        List<Map<String, Object>> tempList;
        tempList = jdbcTemplate.queryForList(String.format("select `%s` 主键, `%s` 外键列\n" +
                "from %s.`%s`", primary, ref_column, dbName, tableName));
        return tempList;
    }

    public List getCount(String dbName, String tableName) {
//        selectdb(dbName);
        List<Map<String, Object>> tempList;
        tempList = jdbcTemplate.queryForList(String.format("select count(*) from %s.`%s`",dbName, tableName));
        return tempList;
    }

    public List selectPrimary(String dbName, String tableName, String column, Object value) {
        // 通过column查找id的值
//        selectdb(dbName);
        List<Map<String, Object>> tempList;
        List<Map<String, Object>> tmp = getPrimary(dbName, tableName);
        // 注意这里有问题，默认value是string类型的，其实并不是
        tempList = jdbcTemplate.queryForList(String.format("select `%s` from %s.`%s` where `%s` = '%s'", tmp.get(0).get("列名"), dbName, tableName, column, value));
        return tempList;
    }
}
